package com.ljqc.sbom.management.repository;

import com.alibaba.fastjson.JSONObject;
import com.ljqc.sbom.management.domain.SpdxProject;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

public interface SpdxProjectRepository extends JpaRepository<SpdxProject, Integer> {

    @Query(nativeQuery = true, value = "SELECT id from spdx_project where name = ?1 and user_id = ?2 LIMIT 1")
    Integer findSdpxProjectIdByNameAndUserId(String projectName, Integer userId);

    @Query(nativeQuery = true, value = "select id from spdx_project where id in ?1")
    List<Integer> filterValidIds(List<String> ids);

    @Query(nativeQuery = true, value = "SELECT " +
            " sp.name, " +
            " sp.id, " +
            " IFNULL(sp.last_scan, 0) last_scan, " +
            " sp.type, " +
            " sp.`status`, " +
            " sp.msg, " +
            " sa.account  " +
            "FROM " +
            " spdx_project sp " +
            " LEFT JOIN sys_account sa ON sp.user_Id = sa.id " +
            " WHERE sp.enterprise_id = ?1  " +
            "  AND IF(COALESCE(?2, NULL) IS NULL, 1 = 1, sp.name LIKE CONCAT('%', ?2, '%')) " +
            "  AND IF(COALESCE(?3, NULL) IS NULL, 1 = 1, sp.repo_vul_level IN (?3))  " +
            "  AND IF(COALESCE(?4, NULL) IS NULL, 1 = 1, sp.user_id = ?4  )" +
            "ORDER BY last_scan DESC",
            countQuery = "SELECT count(*)" +
                    " FROM " +
                    " spdx_project sp " +
                    " LEFT JOIN sys_account sa ON sp.user_Id = sa.id " +
                    " WHERE sp.enterprise_id = ?1  " +
                    "  AND IF(COALESCE(?2, NULL) IS NULL, 1 = 1, sp.name LIKE CONCAT('%', ?2, '%')) " +
                    "  AND IF(COALESCE(?3, NULL) IS NULL, 1 = 1, sp.repo_vul_level IN (?3))  " +
                    "  AND IF(COALESCE(?4, NULL) IS NULL, 1 = 1, sp.user_id = ?4  )" +
                    "ORDER BY last_scan DESC")
    Page<JSONObject> findSpdxProjectList(Integer enterpriseId, String search, List<Integer> repoVulLevelList, Integer userId, Pageable pageable);


    @Query(nativeQuery = true, value = "SELECT " +
            " repo_vul_level AS value, " +
            " count( * ) count  " +
            "FROM " +
            " spdx_project  " +
            "WHERE " +
            " `status` = 2  " +
            " AND enterprise_id = ?1  " +
            " AND repo_vul_level IS NOT NULL  " +
            "  AND IF(COALESCE(?2, NULL) IS NULL, 1 = 1, user_id = ?2  )" +
            "GROUP BY " +
            " repo_vul_level")
    List<JSONObject> getProjectTotalInfo(Integer enterpriseId, Integer userId);

    @Query(nativeQuery = true, value = "select * from spdx_project where id = ?1")
    SpdxProject getSpdxProjectById(Integer id);

    @Modifying
    @Transactional
    @Query(nativeQuery = true, value = "UPDATE `spdx_project` SET msg = ?2, status = ?3 WHERE id=?1 ")
    void updateStatusById(Integer id, String msg, Integer status);
}
